﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class DriverDAL
    {
        private static readonly string ConnectionString =
            ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        /// <summary>
        ///     Register a driver
        /// </summary>
        /// <param name="driver">Represent's the driver</param>
        public static void RegisterDriver(Driver driver)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spAddDriver", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", driver.StaffId);
                command.Parameters.AddWithValue("@LicenseNo", driver.LicenseNo);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All drivers
        /// </summary>
        /// <returns>All drivers</returns>
        public static List<Driver> GetAllDrivers()
        {
            var drivers = new List<Driver>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllDrivers", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var driver = new Driver
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            LicenseNo = reader["LicenseNo"].ToString(),
                            Availability = Convert.ToBoolean(reader["Availability"])
                        };
                        drivers.Add(driver);
                    }
                }
            }
            return drivers;
        }

        /// <summary>
        ///     Get All drivers by subsidiaryId
        /// </summary>
        /// <returns>All drivers</returns>
        public static List<Driver> GetAllDrivers(byte subsidiaryId)
        {
            var drivers = new List<Driver>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetDriversBySubsidiaryId", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var driver = new Driver
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            EMail = reader["EMail"].ToString(),
                            Address = reader["Address"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString(),
                            LicenseNo = reader["LicenseNo"].ToString(),
                            SubsidiaryId = subsidiaryId
                        };

                        if (reader["Availability"] != DBNull.Value)
                        {
                            driver.Availability = Convert.ToBoolean(reader["Availability"]);
                        }

                        drivers.Add(driver);
                    }
                }
            }
            return drivers;
        }

        /// <summary>
        ///     Edit driver's details
        /// </summary>
        /// <param name="driver">Represent's the driver</param>
        public static void EditDriver(Driver driver)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spEditDriver", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", driver.StaffId);
                command.Parameters.AddWithValue("@LicenseNo", driver.LicenseNo);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get driver's license number
        /// </summary>
        /// <param name="staffId">Represent's the staff Id</param>
        /// <returns>License number</returns>
        public static string GetLicenceNo(int staffId)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetDriverLicenseNoById", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", staffId);

                connection.Open();

                var license = command.ExecuteScalar();

                return license != null ? license.ToString() : null;
            }
        }

        /// <summary>
        ///     Set driver's availability (True / False)
        /// </summary>
        /// <param name="driver">Represent's the driver</param>
        public static void SetAvailability(Driver driver)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spSetDriverAvailability", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@StaffId", driver.StaffId);
                command.Parameters.AddWithValue("@Availability", driver.Availability);

                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        ///     Get All available drivers for booking by subsidiaryId
        /// </summary>
        /// <param name="subsidiaryId">Represent's the subsidiary id</param>
        /// <param name="pickupDateTime">Represent's the pickup date and time</param>
        /// <returns>All drivers</returns>
        public static List<Driver> GetAllDrivers(byte subsidiaryId, DateTime pickupDateTime)
        {
            var drivers = new List<Driver>();
            using (var connection = new SqlConnection(ConnectionString))
            {
                var command = new SqlCommand("spGetAllDriversAvailableForBooking", connection)
                {
                    CommandType = CommandType.StoredProcedure
                };

                command.Parameters.AddWithValue("@SubsidiaryId", subsidiaryId);
                command.Parameters.AddWithValue("@PickupDateTime", pickupDateTime);

                connection.Open();
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var driver = new Driver
                        {
                            StaffId = Convert.ToInt32(reader["StaffId"]),
                            FirstName = reader["FirstName"].ToString(),
                            LastName = reader["LastName"].ToString(),
                            EMail = reader["EMail"].ToString(),
                            Address = reader["Address"].ToString(),
                            TelephoneNo = reader["TelephoneNo"].ToString(),
                            Type = reader["Type"].ToString(),
                            LicenseNo = reader["LicenseNo"].ToString(),
                            SubsidiaryId = subsidiaryId
                        };

                        if (reader["Availability"] != DBNull.Value)
                        {
                            driver.Availability = Convert.ToBoolean(reader["Availability"]);
                        }

                        drivers.Add(driver);
                    }
                }
            }
            return drivers;
        }
    }
}